package br.com.cast.syscast.persistencia;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import javax.persistence.Query;

import org.springframework.stereotype.Component;

import br.com.cast.syscast.models.Municipio;
import br.com.cast.syscast.models.Pais;
import br.com.cast.syscast.models.Uf;

@Component 
public class MunicipioDAO extends DAOBase implements IMunicipioDAO {
	
	public MunicipioDAO() {
	}
	
	public List<Municipio> consultaTodos() {
		
		Query query = getEntityManager()
				.createQuery("FROM Municipio");
		
		return query.getResultList();
	}
	
	@Override
	public Iterator<Municipio> consultaPaginada(long inicio, long fim) {
		
		List<Municipio> municipios = new ArrayList<>();
		
		StringBuilder sql = new StringBuilder();
		
		sql.append("SELECT * FROM (")
			.append(" SELECT m.*, ")
			.append("   p.id idPais, p.nome nomePais, ")
			.append("   u.id idUf, u.sigla siglaUf, ")
			.append("  ROW_NUMBER() OVER (ORDER BY m.id) as row")
			.append(" FROM municipio m ")
			.append("	INNER JOIN uf u ")
			.append("	   ON u.id = m.id_uf ")
			.append("	INNER JOIN pais p ")
			.append("	   ON p.id = u.id_pais ")
			.append(") a WHERE a.row > ? and a.row <= ?");
		
		try {
			PreparedStatement ps = getConnection()
					.prepareStatement(sql.toString());
			
			ps.setLong(1, inicio);
			ps.setLong(2, fim);
			
			ResultSet rs = ps.executeQuery();
			
			while (rs.next()) {
				
				Uf uf = new Uf(
						rs.getInt("idUf"), 
						rs.getString("siglaUf"));
				
				uf.setPais(new Pais(
						rs.getInt("idPais"), 
						rs.getString("nomePais")));
				
				Municipio m = new Municipio();
				m.setId(rs.getInt("id"));
				m.setNome(rs.getString("nome"));
				m.setPib(rs.getDouble("pib"));
				m.setPopulacao(rs.getInt("populacao"));
				m.setUf(uf);
				
				municipios.add(m);
			}
			
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
		
		return municipios.iterator();
	}
	
	
	@Override
	public void salvar(Municipio municipio) {
		getEntityManager().persist(municipio);
	}
	
	@Override
	public void alterar(Municipio municipio) {
		getEntityManager().merge(municipio);
	}
	
	@Override
	public long total() {
		String sql = "SELECT COUNT(*) as total "
				+ "FROM municipio";

		Long total = 0L;
		
//		try {
//			PreparedStatement ps = getConnection().prepareStatement(sql);
//			
//			ResultSet rs = ps.executeQuery();
//			
//			if (rs.next()) {
//				total = rs.getLong("total");			
//			}
//			
//		} catch (SQLException e) {
//			throw new RuntimeException(e);
//		}
//		
		return total;
	}
	
	/* (non-Javadoc)
	 * @see br.com.cast.syscast.persistencia.IMunicipioDAO#excluir(int)
	 */
	@Override
	public void excluir(int id) {
		String sql = "DELETE FROM municipio where id = ?";

		try {
			PreparedStatement ps = getConnection().prepareStatement(sql);
			ps.setInt(1, id);
			
			ps.execute();
			
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	/* (non-Javadoc)
	 * @see br.com.cast.syscast.persistencia.IMunicipioDAO#municipioJaCadastrado(br.com.cast.syscast.models.Municipio)
	 */
	@Override
	public boolean municipioJaCadastrado(Municipio municipio) {
		
		String sql = "SELECT COUNT(*) as qtd "
					+ "FROM municipio "
					+ "WHERE nome = ? "
					+ "AND id_uf = ?";
		
		if (municipio.getId() != null) {//alteração
			sql += " AND id <> ? ";
		}

		Long qtd = null;
		
		try {
			PreparedStatement ps = getConnection().prepareStatement(sql);
			
			ps.setString(1, municipio.getNome());
			ps.setInt(2, municipio.getUf().getId());
			
			if (municipio.getId() != null) {//alteração
				ps.setInt(3, municipio.getId());
			}
			
			ResultSet rs = ps.executeQuery();
			
			if (rs.next()) {
				qtd = rs.getLong("qtd");			
			}
			
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
		
		return qtd > 0;
	}
	
}
